package com.lsh.streamgd.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.lsh.streamgd.beans.TaskConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBConnecter {

    private static Logger logger = LoggerFactory.getLogger("DBConnecter");

    public static Map<String, List<String[]>> connectDB_MySql(TaskConfig taskConfig) {
        Connection conn = null;
        try {
            //加载MySql的驱动类
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            conn = DriverManager.getConnection(url, taskConfig.getUserName(), taskConfig.getUserPwd());
        } catch (Exception e) {
            logger.error("加载mysql驱动，获取数据库连接失败！\n{}", StringUtil.getExceptonStack(e));
            return null;
        }
        String sql = "select table_name from information_schema.tables where table_schema= ? and table_type='base table'";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, List<String[]>> table_column_map = new HashMap<>();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, taskConfig.getDbName());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                logger.info("\n table: {}", tableName);
                sql = "select column_name,COLUMN_COMMENT from information_schema.columns where table_schema= ? and table_name= ?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, taskConfig.getDbName());
                pstmt.setString(2, tableName);
                ResultSet rs_colums = pstmt.executeQuery();
                List<String[]> columnNames = new ArrayList<>();
                String columnNameStr = "";
                while (rs_colums.next()) {
                    String[] columnInfo = {rs_colums.getString(1), rs_colums.getString(2)};
                    columnNameStr += "\t" + columnInfo[0] + ":" + columnInfo[1] + "\n";
                    columnNames.add(columnInfo);
                }
                logger.info("\n columns:\n{}", columnNameStr);
                table_column_map.put(tableName, columnNames);
                rs_colums.close();
                pstmt.close();
            }
            return table_column_map;
        } catch (Exception e) {
            logger.error("执行sql出错：{}\n{}", sql, StringUtil.getExceptonStack(e));
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                logger.error("关闭数据库连接错误!\n{}", StringUtil.getExceptonStack(e));
            }
        }
    }

    public static Map<String, List<String[]>> connectDB_PostGre(TaskConfig taskConfig) {
        Connection conn = null;
        try {
            //加载postgresql的驱动类
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            conn = DriverManager.getConnection(url, taskConfig.getUserName(), taskConfig.getUserPwd());
        } catch (Exception e) {
            logger.error("加载postgresql驱动，获取数据库连接失败！\n{}", StringUtil.getExceptonStack(e));
            return null;
        }
        String sql = "select tablename from pg_tables where schemaname='public'";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, List<String[]>> table_column_map = new HashMap<>();
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                logger.info("\n table: {}", tableName);
                sql = "SELECT A.ATTNAME, COL_DESCRIPTION(A.ATTRELID, A.ATTNUM) \n" +
                        "FROM PG_CLASS AS C, PG_ATTRIBUTE AS A \n" +
                        "WHERE C.RELNAME = ? AND A.ATTRELID = C.OID AND A.ATTNUM > 0";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, tableName);
                ResultSet rs_colums = pstmt.executeQuery();
                List<String[]> columnNames = new ArrayList<>();
                String columnNameStr = "";
                while (rs_colums.next()) {
                    String[] columnInfo = {rs_colums.getString(1), rs_colums.getString(2)};
                    columnNameStr += "\t" + columnInfo[0] + ":" + columnInfo[1] + "\n";
                    columnNames.add(columnInfo);
                }
                logger.info("\n columns:\n{}", columnNameStr);
                table_column_map.put(tableName, columnNames);
                rs_colums.close();
                pstmt.close();
            }
            return table_column_map;
        } catch (Exception e) {
            logger.error("执行sql出错：{}\n{}", sql, StringUtil.getExceptonStack(e));
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                logger.error("关闭数据库连接错误!\n{}", StringUtil.getExceptonStack(e));
            }
        }
    }

    public static Map<String, List<String[]>> connectDB_Oracle(TaskConfig taskConfig) {
        Connection conn = null;
        try {
            String url;
            if (taskConfig.getDbConnectType() == Constants.DBConnectType.SID) {
                Class.forName("oracle.jdbc.OracleDriver");
                url = "jdbc:oracle:thin:@" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + ":" + taskConfig.getDbName();
            } else {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                url = "jdbc:oracle:thin:@//" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
            }
            conn = DriverManager.getConnection(url, taskConfig.getUserName(), taskConfig.getUserPwd());
        } catch (Exception e) {
            logger.error("加载oracle驱动，获取数据库连接失败！\n{}", StringUtil.getExceptonStack(e));
            return null;
        }
        String sql = "select table_name from user_tables";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, List<String[]>> table_column_map = new HashMap<>();
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                logger.info("\n table: {}", tableName);
                sql = "select COLUMN_NAME,COMMENTS from user_col_comments where TABLE_NAME  = ?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, tableName);
                ResultSet rs_colums = pstmt.executeQuery();
                List<String[]> columnNames = new ArrayList<>();
                String columnNameStr = "";
                while (rs_colums.next()) {
                    String[] columnInfo = {rs_colums.getString(1), rs_colums.getString(2)};
                    columnNameStr += "\t" + columnInfo[0] + ":" + columnInfo[1] + "\n";
                    columnNames.add(columnInfo);
                }
                logger.info("\n columns:\n{}", columnNameStr);
                table_column_map.put(taskConfig.getUserName().toUpperCase() + "." + tableName, columnNames);
                rs_colums.close();
                pstmt.close();
            }
            return table_column_map;
        } catch (Exception e) {
            logger.error("执行sql出错：{}\n{}", sql, StringUtil.getExceptonStack(e));
            return null;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                logger.error("关闭数据库连接错误!\n{}", StringUtil.getExceptonStack(e));
            }
        }
    }

    public static void getMaxColumnValue(TaskConfig taskConfig) {
        JSONObject result = new JSONObject();
        try {
            JSONArray ja = JSONArray.parseArray(taskConfig.getWhiteNameColumns());
            for (int i = 0; i < ja.size(); i++) {
                String updateColumn = ja.getJSONObject(i).getString("updateColumn");
                if (StringUtil.isBlank(updateColumn)) {
                    continue;
                }
                String tableName = ja.getJSONObject(i).getString("tableName");
                String url;
                if (taskConfig.getDbType() == Constants.DBType.MYSQL) {
                    Class.forName("com.mysql.jdbc.Driver");
                    url = "jdbc:mysql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
                } else if (taskConfig.getDbType() == Constants.DBType.POSTGRES) {
                    Class.forName("org.postgresql.Driver");
                    url = "jdbc:postgresql://" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
                } else if (taskConfig.getDbType() == Constants.DBType.ORACLE) {
                    if (taskConfig.getDbConnectType() == Constants.DBConnectType.SID) {
                        Class.forName("oracle.jdbc.OracleDriver");
                        url = "jdbc:oracle:thin:@" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + ":" + taskConfig.getDbName();
                    } else {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                        url = "jdbc:oracle:thin:@//" + taskConfig.getIpAddress() + ":" + taskConfig.getPortNo() + "/" + taskConfig.getDbName();
                    }
                }else{
                    return;
                }
                Connection conn = DriverManager.getConnection(url, taskConfig.getUserName(), taskConfig.getUserPwd());
                PreparedStatement pstmt = conn.prepareStatement("select max(" + updateColumn + ") from " + tableName);
                ResultSet rs_colums = pstmt.executeQuery();
                if (rs_colums.next()) {
                    result.put(tableName, rs_colums.getString(1));
                }
            }
            taskConfig.setMaxUpdateColumnValue(result.toJSONString());
        } catch (Exception e) {
            logger.error("\n=====[  getMaxColumnValue()异常  ]=====\n{}", StringUtil.getExceptonStack(e));
        }
    }

}
